﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace ExamSystem.DAL
{
    public class DBhepter
    {
        /// <summary>
        /// 数据库连接帮助类
        /// </summary>
        static string connstring = "server=.;database=ExamSystem;uid=sa;pwd=123456";

            /// <summary>
            /// 执行增删改操作
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public static int ExecuteNonQuery(string sql)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //质量对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //执行
                int i = cmd.ExecuteNonQuery();
                //关闭
                conn.Close();
                return i;
            }

            /// <summary>
            /// 执行增删改操作
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="sps"></param>
            /// <returns></returns>
            public static int ExecuteNonQuery(string sql, SqlParameter[] sps)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //质量对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //把参数添加到质量对象中
                cmd.Parameters.AddRange(sps);
                //执行
                int i = cmd.ExecuteNonQuery();
                //关闭
                conn.Close();
                return i;
            }



            /// <summary>
            /// 执行查询，获取第一行第一列的值
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public static object ExecuteScalar(string sql)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //质量对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //执行
                object obj = cmd.ExecuteScalar();
                //关闭
                conn.Close();
                return obj;
            }

            /// <summary>
            ///  执行查询，获取第一行第一列的值
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="sps"></param>
            /// <returns></returns>
            public static object ExecuteScalar(string sql, SqlParameter[] sps)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //质量对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //把参数添加到质量对象中
                cmd.Parameters.AddRange(sps);
                //执行
                object obj = cmd.ExecuteScalar();
                //关闭
                conn.Close();
                return obj;
            }

            /// <summary>
            /// 执行查询，获取多行多列
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public static DataSet GetDataSet(string sql)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //adapter对象
                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                //数据集对象
                DataSet ds = new DataSet();
                //填充
                adapter.Fill(ds);
                //关闭
                conn.Close();
                return ds;
            }

            /// <summary>
            /// 执行查询，获取多行多列
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="sps"></param>
            /// <returns></returns>
            public static DataSet GetDataSet(string sql, SqlParameter[] sps)
            {
                //连接对象
                SqlConnection conn = new SqlConnection(connstring);
                //打开链接
                conn.Open();
                //质量对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //把参数添加到质量对象中
                cmd.Parameters.AddRange(sps);
                //adapter对象
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //数据集对象
                DataSet ds = new DataSet();
                //填充
                adapter.Fill(ds);
                //关闭
                conn.Close();
                return ds;
            }


            /// <summary>
            /// 执行事务
            /// </summary>
            /// <param name="sqls"></param>
            /// <returns></returns>
            public static bool ExecuteTransaction(List<string> sqls)
            {
                bool result = true;
                SqlConnection conn = null;
                SqlTransaction tran = null;
                try
                {
                    //连接对象
                    conn = new SqlConnection(connstring);
                    //打开链接
                    conn.Open();
                    //事务对象
                    tran = conn.BeginTransaction();

                    //循环执行
                    foreach (string sql in sqls)
                    {
                        //指令对象
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        //把指令对象添加到事务中
                        cmd.Transaction = tran;
                        //执行
                        cmd.ExecuteNonQuery();
                    }
                    //提交事务
                    tran.Commit();
                }
                catch(Exception ex)
                {
                    result = false;
                    if (tran != null) tran.Rollback();//回滚
                }
                finally
                {
                    if (conn != null) conn.Close();//关闭
                }

                return result;

            }

            /// <summary>
            /// 执行事务
            /// </summary>
            /// <param name="sqls"></param>
            /// <param name="sps"></param>
            /// <returns></returns>
            public static bool ExecuteTransaction(List<string> sqls, List<SqlParameter[]> sps)
            {
                bool result = true;
                SqlConnection conn = null;
                SqlTransaction tran = null;
                try
                {
                    //连接对象
                    conn = new SqlConnection(connstring);
                    //打开链接
                    conn.Open();
                    //事务对象
                    tran = conn.BeginTransaction();

                    //循环执行
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        string sql = sqls[i];
                        SqlParameter[] sp = sps[i];
                        //指令对象
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        //把参数添加到指令对象中
                        cmd.Parameters.AddRange(sp);
                        //把指令对象添加到事务中
                        cmd.Transaction = tran;
                        //执行
                        cmd.ExecuteNonQuery();
                    }
                    //提交事务
                    tran.Commit();
                }
                catch
                {
                    result = false;
                    if (tran != null) tran.Rollback();//回滚
                }
                finally
                {
                    if (conn != null) conn.Close();//关闭
                }

                return result;

            }
        }
    
}